package com.bt.lms.dao.servise.impl;

import com.bt.lms.entity.Major;
import com.bt.lms.entity.StudyCen;
import com.bt.lms.entity.page.Page;
import com.bt.lms.dao.servise.StudyCenDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 学习中心服务dao实现
 *
 * @author 徐大永
 */
public class StudyCenDaoImpl implements StudyCenDao {

    @Override
    public int insert(String sql, StudyCen studyCen) {

        //需要入库的属性
        String name = studyCen.getName();
        String code = studyCen.getCode();
        String relationPen = studyCen.getRelationPen();
        String phone = studyCen.getPhone();
        String email = studyCen.getEmail();
        String address = studyCen.getAddress();
        String describe = studyCen.getDescribe();
        try {
            //1.获取连接
            Connection connection = UserDaoImpl.pond();
            //2.域加载对象
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, code);
            preparedStatement.setString(3, relationPen);
            preparedStatement.setString(4, phone);
            preparedStatement.setString(5, email);
            preparedStatement.setString(6, address);
            preparedStatement.setString(7, describe);
            //3.执行SQL
            int i = preparedStatement.executeUpdate();
            //4.关闭资源
            preparedStatement.close();
            connection.close();

            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void select(String studyCenSql, StudyCen studyCen) {

        Long id = studyCen.getId();
        try {
            //连接
            Connection connection = UserDaoImpl.pond();
            //预编译
            PreparedStatement preparedStatement = connection.prepareStatement(studyCenSql);
            preparedStatement.setLong(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            boolean next = resultSet.next();
            if (next) {
                String name = resultSet.getString("name");
                String code = resultSet.getString("code");
                String relation = resultSet.getString("relation");
                String phone = resultSet.getString("phone");
                String email = resultSet.getString("email");
                String describe = resultSet.getString("describe_");
                String address = resultSet.getString("address");
                //带走
                studyCen.setName(name);
                studyCen.setCode(code);
                studyCen.setRelationPen(relation);
                studyCen.setPhone(phone);
                studyCen.setEmail(email);
                studyCen.setDescribe(describe);
                studyCen.setAddress(address);
                //资源关闭
                preparedStatement.close();
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Long> selectRl(String rlSql, Long id) {
        //要返回的对象
        List<Long> longs = new ArrayList<>();
        try {
            //学习中心-专业关系表
            //获取连接
            Connection conRl = UserDaoImpl.pond();
            //域加载
            PreparedStatement preparedStatement = conRl.prepareStatement(rlSql);
            preparedStatement.setLong(1, id);
            //执行
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                long majorId = resultSet.getLong("major_id");
                longs.add(majorId);
            }
            //关闭资源
            preparedStatement.close();
            conRl.close();
            return longs;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Major select(String majorSql, Long id) {

        //要返回的对象
        Major major = new Major();

        try {
            //专业表
            //获取连接
            Connection conMajor = UserDaoImpl.pond();
            //域加载
            PreparedStatement staMajor = conMajor.prepareStatement(majorSql);
            staMajor.setLong(1, id);
            //执行SQL
            ResultSet majorRs = staMajor.executeQuery();
            majorRs.next();
            String majorName = majorRs.getString("name");
            //关闭资源
            staMajor.close();
            conMajor.close();

            //返回对象
            major.setName(majorName);
            return major;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public void select(String sql, Page<StudyCen> page, String sqlSum) {

        //需要处理的数据
        List<StudyCen> studyCenS = new ArrayList<>();
        page.setRecords(studyCenS);

        //查询所需数据
        Integer size = page.getSize();//需要查询出的数量
        Integer current = page.getCurrent();
        int startIndex = size*(current-1);//开始位置

        //连接
        Connection connection = UserDaoImpl.pond();
        try {
            //域处理
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,startIndex);
            preparedStatement.setInt(2,size);

            //执行总数
            ResultSet sumRS = preparedStatement.executeQuery(sqlSum);
            sumRS.next();
            long total = sumRS.getLong(1);
            page.setTotal(total);

            //执行具体数据
            ResultSet dataRS = preparedStatement.executeQuery();
            while (dataRS.next()){

                //带走数据的人
                StudyCen studyCen = new StudyCen();
                studyCenS.add(studyCen);

                //具体数据（名字，编码，联系人，移动电话，电子邮箱）
                long id = dataRS.getLong("id");
                String name = dataRS.getString("name");
                String code = dataRS.getString("code");
                String relation = dataRS.getString("relation");
                String phone = dataRS.getString("phone");
                String email = dataRS.getString("email");

                //带走
                studyCen.setId(id);
                studyCen.setName(name);
                studyCen.setCode(code);
                studyCen.setRelationPen(relation);
                studyCen.setPhone(phone);
                studyCen.setEmail(email);

            }

            //关闭资源
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public int update(String sql, StudyCen studyCen) {

        //要编辑的属性 UPDATE study_centre SET name=?,code=?,relation=?,phone=?,email=?,describe_=?,address=? WHERE id=?
        String name = studyCen.getName();
        String code = studyCen.getCode();
        String relationPen = studyCen.getRelationPen();
        String phone = studyCen.getPhone();
        String email = studyCen.getEmail();
        String describe = studyCen.getDescribe();
        String address = studyCen.getAddress();
        Long id = studyCen.getId();


        //连接
        Connection connection = UserDaoImpl.pond();
        try {
            //预编译
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, code);
            preparedStatement.setString(3, relationPen);
            preparedStatement.setString(4, phone);
            preparedStatement.setString(5, email);
            preparedStatement.setString(6, describe);
            preparedStatement.setString(7, address);
            preparedStatement.setLong(8, id);
            //执行
            int i = preparedStatement.executeUpdate();
            //关闭资源
            preparedStatement.close();
            connection.close();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int update(String sql, Long id) {

        try {
            //连接
            Connection connection = UserDaoImpl.pond();
            //预编译
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, id);
            //执行
            int i = preparedStatement.executeUpdate();
            //关闭资源
            preparedStatement.close();
            connection.close();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
